#!/bin/bash

# 项目集进度管理系统 - 数据库恢复脚本
# 用于从备份文件恢复MySQL数据库

# 配置变量
DB_HOST="localhost"
DB_PORT="3306"
DB_NAME="project_management_system"
DB_USER="your_username"
DB_PASSWORD="your_password"

# 备份目录
BACKUP_DIR="/backup/mysql"

# 日志文件
LOG_FILE="/var/log/mysql/restore.log"

# 函数：显示使用说明
show_usage() {
    echo "使用方法: $0 [选项] <备份文件>"
    echo "选项:"
    echo "  -h, --help          显示此帮助信息"
    echo "  -l, --list          列出可用的备份文件"
    echo "  -f, --force         强制恢复（不提示确认）"
    echo "  --drop-database     恢复前删除现有数据库"
    echo ""
    echo "示例:"
    echo "  $0 project_management_system_20240115_120000.sql.gz"
    echo "  $0 -f /backup/mysql/project_management_system_20240115_120000.sql.gz"
    echo "  $0 --list"
}

# 函数：记录日志
log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}

# 函数：检查命令执行结果
check_result() {
    if [ $? -eq 0 ]; then
        log_message "SUCCESS: $1"
    else
        log_message "ERROR: $1"
        exit 1
    fi
}

# 函数：列出可用的备份文件
list_backups() {
    echo "可用的备份文件:"
    if [ -d "$BACKUP_DIR" ]; then
        ls -lht "$BACKUP_DIR"/project_management_system_*.sql.gz 2>/dev/null | head -20
        if [ $? -ne 0 ]; then
            echo "未找到备份文件"
        fi
    else
        echo "备份目录不存在: $BACKUP_DIR"
    fi
}

# 函数：确认操作
confirm_restore() {
    echo "警告: 此操作将覆盖现有数据库 '$DB_NAME'"
    echo "备份文件: $1"
    echo -n "确定要继续吗? (yes/no): "
    read -r response
    if [ "$response" != "yes" ]; then
        echo "操作已取消"
        exit 0
    fi
}

# 函数：检查备份文件
check_backup_file() {
    local backup_file="$1"
    
    # 检查文件是否存在
    if [ ! -f "$backup_file" ]; then
        log_message "ERROR: 备份文件不存在: $backup_file"
        exit 1
    fi
    
    # 检查文件完整性
    if [[ "$backup_file" == *.gz ]]; then
        log_message "检查压缩文件完整性..."
        gunzip -t "$backup_file"
        check_result "备份文件完整性验证"
    fi
    
    log_message "备份文件检查通过: $backup_file"
}

# 函数：执行数据库恢复
restore_database() {
    local backup_file="$1"
    local drop_db="$2"
    
    log_message "开始数据库恢复..."
    
    # 如果需要，删除现有数据库
    if [ "$drop_db" = "true" ]; then
        log_message "删除现有数据库: $DB_NAME"
        mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASSWORD" \
            -e "DROP DATABASE IF EXISTS \`$DB_NAME\`;"
        check_result "删除现有数据库"
        
        log_message "创建新数据库: $DB_NAME"
        mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASSWORD" \
            -e "CREATE DATABASE \`$DB_NAME\` DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;"
        check_result "创建新数据库"
    fi
    
    # 恢复数据库
    log_message "正在恢复数据库: $DB_NAME"
    if [[ "$backup_file" == *.gz ]]; then
        # 压缩文件
        gunzip -c "$backup_file" | mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASSWORD" "$DB_NAME"
    else
        # 普通SQL文件
        mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" < "$backup_file"
    fi
    
    check_result "数据库恢复"
    
    # 验证恢复结果
    log_message "验证恢复结果..."
    table_count=$(mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" \
        -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='$DB_NAME';" -s -N)
    
    log_message "恢复完成，数据库包含 $table_count 个表"
}

# 解析命令行参数
FORCE_RESTORE=false
DROP_DATABASE=false
BACKUP_FILE=""

while [[ $# -gt 0 ]]; do
    case $1 in
        -h|--help)
            show_usage
            exit 0
            ;;
        -l|--list)
            list_backups
            exit 0
            ;;
        -f|--force)
            FORCE_RESTORE=true
            shift
            ;;
        --drop-database)
            DROP_DATABASE=true
            shift
            ;;
        -*)
            echo "未知选项: $1"
            show_usage
            exit 1
            ;;
        *)
            BACKUP_FILE="$1"
            shift
            ;;
    esac
done

# 检查是否提供了备份文件
if [ -z "$BACKUP_FILE" ]; then
    echo "错误: 请指定备份文件"
    show_usage
    exit 1
fi

# 如果备份文件不是绝对路径，则在备份目录中查找
if [[ "$BACKUP_FILE" != /* ]]; then
    BACKUP_FILE="$BACKUP_DIR/$BACKUP_FILE"
fi

# 检查备份文件
check_backup_file "$BACKUP_FILE"

# 确认恢复操作
if [ "$FORCE_RESTORE" != "true" ]; then
    confirm_restore "$BACKUP_FILE"
fi

# 执行恢复
restore_database "$BACKUP_FILE" "$DROP_DATABASE"

log_message "数据库恢复完成: $BACKUP_FILE"

# 显示恢复后的数据库信息
log_message "数据库恢复后的统计信息:"
mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" \
    -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='$DB_NAME' AND table_type='BASE TABLE';" \
    | tee -a "$LOG_FILE"

exit 0